﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data.Common;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Web;
namespace Guru.Data
{
    public class GuruDatabase
    {
        #region Init Constructors

        private string _ConnectionString;
        private Database _Database = null;
        private DbCommand _DBCommand = null;

        public GuruDatabase(int clientId)
        {
            InitDatabase(clientId);
        }

        public GuruDatabase(string clientId)
        {
            InitDatabase(clientId);
        }

        public GuruDatabase()
        {
            InitDatabase(0);
        }

        public GuruDatabase(string key, bool webConfig)
        {
            _ConnectionString = GuruConnectionString.GetConnectionStrings(key, webConfig);
            _Database = new SqlDatabase(_ConnectionString);
        }

        public GuruDatabase(DatabaseType type)
        {
            _Database = DatabaseFactory.CreateDatabase();
        }

        public GuruDatabase(DatabaseType type, string name)
        {
            _Database = DatabaseFactory.CreateDatabase(name);
        }

        private void InitDatabase(int clientId)
        {
            InitDatabase(clientId.ToString());
        }

        private void InitDatabase(string clientId)
        {
            _ConnectionString = GuruConnectionString.GetConnectionStrings(clientId);
            _Database = new SqlDatabase(_ConnectionString);
        }

        public void Dispose()
        {
            if (_DBCommand != null)
            {
                _DBCommand.Dispose();
                _DBCommand = null;
            }
            if (_Database != null)
            {
                _Database = null;
            }
        }

        #endregion

        #region Get DataSet

        public DataSet GetDataSet(string storedProcedureName, GuruParameterCollection paramsList)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);
            foreach (GuruParameter param in paramsList)
            {
                _Database.AddInParameter(_DBCommand, param);
            }
            DataSet resultList = _Database.ExecuteDataSet(_DBCommand);
            return resultList;
        }

        public DataSet GetDataSet(string storedProcedureName, GuruParameterCollection paramsList, out GuruParameterCollection outParams)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);
            foreach (GuruParameter param in paramsList)
            {
                if (param.IsOutParameter)
                    _Database.AddOutParameter(_DBCommand, param);
                else
                    _Database.AddInParameter(_DBCommand, param);
            }
            DataSet resultList = _Database.ExecuteDataSet(_DBCommand);

            //Get output parameters list
            outParams = new GuruParameterCollection();
            GuruParameter tempParam = null;
            foreach (GuruParameter param in paramsList)
            {
                if (param.IsOutParameter)
                {
                    // get output values and set to parameter value
                    tempParam = param.Clone();
                    tempParam.ParameterValue = _Database.GetParameterValue(_DBCommand, param.ParameterName);
                    outParams.Add(tempParam);
                }
            }

            return resultList;
        }

        public DataSet GetDataSet(string storedProcedureName, params object[] paramValues)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName, paramValues);
            DataSet resultList = _Database.ExecuteDataSet(_DBCommand);
            return resultList;
        }

        public DataSet GetDataSet(string storedProcedureName)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);
            DataSet resultList = _Database.ExecuteDataSet(_DBCommand);
            return resultList;
        }

        #endregion

        #region Get DataTable

        public DataTable GetDataTable(string storedProcedureName, GuruParameterCollection paramsList)
        {
            DataSet resultList = GetDataSet(storedProcedureName, paramsList);
            return resultList.ToDataTable();
        }

        public DataTable GetDataTable(string storedProcedureName, GuruParameterCollection paramsList, out GuruParameterCollection outParams)
        {
            DataSet resultList = GetDataSet(storedProcedureName, paramsList, out outParams);
            return resultList.ToDataTable();
        }

        public DataTable GetDataTable(string storedProcedureName, params object[] paramValues)
        {
            DataSet resultList = GetDataSet(storedProcedureName, paramValues);
            return resultList.ToDataTable();
        }

        public DataTable GetDataTable(string storedProcedureName)
        {
            DataSet resultList = GetDataSet(storedProcedureName);
            return resultList.ToDataTable();
        }

        #endregion

        #region Get DataReader

        public IDataReader GetDataReader(string storedProcedureName, GuruParameterCollection paramsList)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);
            foreach (GuruParameter param in paramsList)
            {
                _Database.AddInParameter(_DBCommand, param);
            }
            var resultList = _Database.ExecuteReader(_DBCommand);
            return resultList;
        }

        #endregion

        #region Update Data

        public int UpdateData(string storedProcedureName, GuruParameterCollection paramsList, out GuruParameterCollection outParams)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);

            foreach (GuruParameter param in paramsList)
            {
                if (param.IsOutParameter) 
                    _Database.AddOutParameter(_DBCommand, param.ParameterName, (DbType)Enum.ToObject(typeof(DbType), param.ParameterType), 100);
                else 
                    _Database.AddInParameter(_DBCommand, param.ParameterName, (DbType)Enum.ToObject(typeof(DbType), param.ParameterType), param.ParameterValue);
            }
            int affectedRows = _Database.ExecuteNonQuery(_DBCommand);

            //Get output parameters list
            outParams = new GuruParameterCollection();
            GuruParameter tempParam = null;
            foreach (GuruParameter param in paramsList)
            {
                if (param.IsOutParameter)
                {
                    tempParam = param.Clone();
                    tempParam.ParameterValue = _Database.GetParameterValue(_DBCommand, param.ParameterName);
                    outParams.Add(tempParam);
                }
            }
            return affectedRows;
        }

        

        public int UpdateData(string storedProcedureName, GuruParameterCollection paramsList)
        {
            GuruParameterCollection outParams = new GuruParameterCollection();
            return UpdateData(storedProcedureName, paramsList, out outParams);
        }

        public int UpdateData(string storedProcedureName)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName);
            int affectedRows = _Database.ExecuteNonQuery(_DBCommand);
            return affectedRows;
        }

        public int UpdateData(string storedProcedureName, params object[] paramsList)
        {
            _DBCommand = _Database.GetStoredProcCommand(storedProcedureName, paramsList);
            int affectedRows = _Database.ExecuteNonQuery(_DBCommand);
            return affectedRows;
        }

        public object GetParamOut(string paramName)
        {
            return _Database.GetParameterValue(_DBCommand, paramName);
        }

        #endregion

    }
}



public enum DatabaseType
{
    SQL,
    Oracle,
    All
}
